"""
企业级金融数据MCP服务系统 - 股票数据模型
提供完整的股票相关数据SQLAlchemy ORM模型定义
"""

from datetime import datetime, date
from decimal import Decimal
from typing import Optional, Dict, Any
from sqlalchemy import String, DateTime, Date, Boolean, Integer, Numeric, Text, Index, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.dialects.postgresql import JSONB, ARRAY

from .base import BaseFinancialModel, DataVersionMixin, SyncStatusMixin, PartitionMixin


class StockBasicInfo(BaseFinancialModel, SyncStatusMixin):
    """
    股票基本信息模型
    存储股票的基础信息，如股票代码、名称、上市日期等
    """
    __tablename__ = "stock_basic_info"
    
    # 股票代码（主键之一）
    stock_code: Mapped[str] = mapped_column(String(20), nullable=False, comment="股票代码")
    
    # 股票名称
    stock_name: Mapped[str] = mapped_column(String(100), nullable=False, comment="股票名称")
    
    # 交易所代码：SH(上海), SZ(深圳), BJ(北京)
    exchange: Mapped[str] = mapped_column(String(10), nullable=False, comment="交易所代码")
    
    # 市场类型：主板, 中小板, 创业板, 科创板, 新三板
    market_type: Mapped[Optional[str]] = mapped_column(String(20), nullable=True, comment="市场类型")
    
    # 行业分类
    industry: Mapped[Optional[str]] = mapped_column(String(100), nullable=True, comment="行业分类")
    
    # 概念板块
    concept: Mapped[Optional[str]] = mapped_column(String(500), nullable=True, comment="概念板块")
    
    # 上市日期
    list_date: Mapped[Optional[date]] = mapped_column(Date, nullable=True, comment="上市日期")
    
    # 退市日期
    delist_date: Mapped[Optional[date]] = mapped_column(Date, nullable=True, comment="退市日期")
    
    # 股票状态：L(上市), D(退市), P(暂停上市)
    list_status: Mapped[str] = mapped_column(String(10), nullable=False, default="L", comment="股票状态")
    
    # 总股本（万股）
    total_share: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="总股本(万股)")
    
    # 流通股本（万股）
    float_share: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="流通股本(万股)")
    
    # 注册资本（万元）
    reg_capital: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="注册资本(万元)")
    
    # 公司简介
    company_profile: Mapped[Optional[str]] = mapped_column(Text, nullable=True, comment="公司简介")
    
    # 主营业务
    main_business: Mapped[Optional[str]] = mapped_column(Text, nullable=True, comment="主营业务")
    
    # 扩展信息
    extra_info: Mapped[Optional[Dict[str, Any]]] = mapped_column(JSONB, nullable=True, comment="扩展信息")
    
    __table_args__ = (
        UniqueConstraint('stock_code', 'exchange', name='uk_stock_basic_code_exchange'),
        Index('idx_stock_basic_exchange', 'exchange'),
        Index('idx_stock_basic_industry', 'industry'),
        Index('idx_stock_basic_list_date', 'list_date'),
        Index('idx_stock_basic_status', 'list_status'),
    )
    
    def __repr__(self) -> str:
        return f"<StockBasicInfo(stock_code={self.stock_code}, stock_name={self.stock_name}, exchange={self.exchange})>"


class StockDailyData(BaseFinancialModel, PartitionMixin):
    """
    股票日线数据模型
    存储股票的日K线数据，包括开高低收、成交量、成交额等
    """
    __tablename__ = "stock_daily_data"
    
    # 股票代码
    stock_code: Mapped[str] = mapped_column(String(20), nullable=False, comment="股票代码")
    
    # 交易日期
    trade_date: Mapped[date] = mapped_column(Date, nullable=False, comment="交易日期")
    
    # 开盘价
    open_price: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 3), nullable=True, comment="开盘价")
    
    # 最高价
    high_price: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 3), nullable=True, comment="最高价")
    
    # 最低价
    low_price: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 3), nullable=True, comment="最低价")
    
    # 收盘价
    close_price: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 3), nullable=True, comment="收盘价")
    
    # 前收盘价
    pre_close: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 3), nullable=True, comment="前收盘价")
    
    # 涨跌额
    change: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 3), nullable=True, comment="涨跌额")
    
    # 涨跌幅(%)
    pct_chg: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 4), nullable=True, comment="涨跌幅(%)")
    
    # 成交量（手）
    volume: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 2), nullable=True, comment="成交量(手)")
    
    # 成交额（千元）
    amount: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 2), nullable=True, comment="成交额(千元)")
    
    # 换手率(%)
    turnover_rate: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 4), nullable=True, comment="换手率(%)")
    
    # 市盈率
    pe_ratio: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 4), nullable=True, comment="市盈率")
    
    # 市净率
    pb_ratio: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 4), nullable=True, comment="市净率")
    
    # 总市值（万元）
    total_mv: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 2), nullable=True, comment="总市值(万元)")
    
    # 流通市值（万元）
    circ_mv: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 2), nullable=True, comment="流通市值(万元)")
    
    # 复权因子
    adj_factor: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 6), nullable=True, comment="复权因子")
    
    __table_args__ = (
        UniqueConstraint('stock_code', 'trade_date', name='uk_stock_daily_code_date'),
        Index('idx_stock_daily_code', 'stock_code'),
        Index('idx_stock_daily_date', 'trade_date'),
        Index('idx_stock_daily_code_date', 'stock_code', 'trade_date'),
        # 分区索引
        Index('idx_stock_daily_partition_date', 'partition_date'),
    )
    
    def __repr__(self) -> str:
        return f"<StockDailyData(stock_code={self.stock_code}, trade_date={self.trade_date}, close_price={self.close_price})>"


class StockMinuteData(BaseFinancialModel, PartitionMixin):
    """
    股票分钟数据模型
    存储股票的分钟级K线数据
    """
    __tablename__ = "stock_minute_data"
    
    # 股票代码
    stock_code: Mapped[str] = mapped_column(String(20), nullable=False, comment="股票代码")
    
    # 交易时间
    trade_time: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, comment="交易时间")
    
    # 开盘价
    open_price: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 3), nullable=True, comment="开盘价")
    
    # 最高价
    high_price: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 3), nullable=True, comment="最高价")
    
    # 最低价
    low_price: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 3), nullable=True, comment="最低价")
    
    # 收盘价
    close_price: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 3), nullable=True, comment="收盘价")
    
    # 成交量（手）
    volume: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 2), nullable=True, comment="成交量(手)")
    
    # 成交额（元）
    amount: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 2), nullable=True, comment="成交额(元)")
    
    # 分钟类型：1min, 5min, 15min, 30min, 60min
    minute_type: Mapped[str] = mapped_column(String(10), nullable=False, default="1min", comment="分钟类型")
    
    __table_args__ = (
        UniqueConstraint('stock_code', 'trade_time', 'minute_type', name='uk_stock_minute_code_time_type'),
        Index('idx_stock_minute_code', 'stock_code'),
        Index('idx_stock_minute_time', 'trade_time'),
        Index('idx_stock_minute_type', 'minute_type'),
        Index('idx_stock_minute_code_time', 'stock_code', 'trade_time'),
        # 分区索引
        Index('idx_stock_minute_partition_date', 'partition_date'),
    )
    
    def __repr__(self) -> str:
        return f"<StockMinuteData(stock_code={self.stock_code}, trade_time={self.trade_time}, minute_type={self.minute_type})>"


class StockTickData(BaseFinancialModel, PartitionMixin):
    """
    股票逐笔数据模型
    存储股票的tick级别交易数据
    """
    __tablename__ = "stock_tick_data"
    
    # 股票代码
    stock_code: Mapped[str] = mapped_column(String(20), nullable=False, comment="股票代码")
    
    # 交易时间
    trade_time: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, comment="交易时间")
    
    # 成交价
    price: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 3), nullable=True, comment="成交价")
    
    # 成交量（手）
    volume: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 2), nullable=True, comment="成交量(手)")
    
    # 买一价
    bid1: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 3), nullable=True, comment="买一价")
    
    # 买一量
    bid1_size: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 2), nullable=True, comment="买一量")
    
    # 卖一价
    ask1: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 3), nullable=True, comment="卖一价")
    
    # 卖一量
    ask1_size: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 2), nullable=True, comment="卖一量")
    
    # 买卖方向：B(买), S(卖), N(中性)
    direction: Mapped[Optional[str]] = mapped_column(String(1), nullable=True, comment="买卖方向")
    
    # 成交类型：0(普通成交), 1(大宗交易), 2(盘后定价)
    trade_type: Mapped[Optional[Integer]] = mapped_column(Integer, nullable=True, comment="成交类型")
    
    __table_args__ = (
        Index('idx_stock_tick_code', 'stock_code'),
        Index('idx_stock_tick_time', 'trade_time'),
        Index('idx_stock_tick_code_time', 'stock_code', 'trade_time'),
        # 分区索引
        Index('idx_stock_tick_partition_date', 'partition_date'),
    )
    
    def __repr__(self) -> str:
        return f"<StockTickData(stock_code={self.stock_code}, trade_time={self.trade_time}, price={self.price})>"


class StockFinancialData(BaseFinancialModel, DataVersionMixin):
    """
    股票财务数据模型
    存储股票的财务报表数据
    """
    __tablename__ = "stock_financial_data"
    
    # 股票代码
    stock_code: Mapped[str] = mapped_column(String(20), nullable=False, comment="股票代码")
    
    # 报告期
    report_period: Mapped[date] = mapped_column(Date, nullable=False, comment="报告期")
    
    # 报告类型：Q1(一季报), Q2(中报), Q3(三季报), Q4(年报)
    report_type: Mapped[str] = mapped_column(String(10), nullable=False, comment="报告类型")
    
    # 公告日期
    ann_date: Mapped[Optional[date]] = mapped_column(Date, nullable=True, comment="公告日期")
    
    # 营业收入（万元）
    revenue: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="营业收入(万元)")
    
    # 净利润（万元）
    net_profit: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="净利润(万元)")
    
    # 扣非净利润（万元）
    net_profit_dedt: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="扣非净利润(万元)")
    
    # 总资产（万元）
    total_assets: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="总资产(万元)")
    
    # 净资产（万元）
    total_equity: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="净资产(万元)")
    
    # 总负债（万元）
    total_liab: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="总负债(万元)")
    
    # 经营活动现金流（万元）
    operate_cash_flow: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="经营活动现金流(万元)")
    
    # 每股收益
    eps: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 4), nullable=True, comment="每股收益")
    
    # 每股净资产
    bps: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 4), nullable=True, comment="每股净资产")
    
    # ROE(%)
    roe: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 4), nullable=True, comment="ROE(%)")
    
    # ROA(%)
    roa: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 4), nullable=True, comment="ROA(%)")
    
    # 毛利率(%)
    gross_margin: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 4), nullable=True, comment="毛利率(%)")
    
    # 净利率(%)
    net_margin: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 4), nullable=True, comment="净利率(%)")
    
    # 资产负债率(%)
    debt_ratio: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 4), nullable=True, comment="资产负债率(%)")
    
    # 详细财务数据
    financial_details: Mapped[Optional[Dict[str, Any]]] = mapped_column(JSONB, nullable=True, comment="详细财务数据")
    
    __table_args__ = (
        UniqueConstraint('stock_code', 'report_period', 'report_type', name='uk_stock_financial_code_period_type'),
        Index('idx_stock_financial_code', 'stock_code'),
        Index('idx_stock_financial_period', 'report_period'),
        Index('idx_stock_financial_type', 'report_type'),
        Index('idx_stock_financial_ann_date', 'ann_date'),
    )
    
    def __repr__(self) -> str:
        return f"<StockFinancialData(stock_code={self.stock_code}, report_period={self.report_period}, report_type={self.report_type})>"


class StockHolderData(BaseFinancialModel):
    """
    股票股东数据模型
    存储股票的股东信息
    """
    __tablename__ = "stock_holder_data"
    
    # 股票代码
    stock_code: Mapped[str] = mapped_column(String(20), nullable=False, comment="股票代码")
    
    # 报告期
    report_period: Mapped[date] = mapped_column(Date, nullable=False, comment="报告期")
    
    # 股东名称
    holder_name: Mapped[str] = mapped_column(String(200), nullable=False, comment="股东名称")
    
    # 持股数量（万股）
    hold_amount: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="持股数量(万股)")
    
    # 持股比例(%)
    hold_ratio: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 4), nullable=True, comment="持股比例(%)")
    
    # 股东类型：个人, 机构, 基金, 社保, QFII等
    holder_type: Mapped[Optional[str]] = mapped_column(String(50), nullable=True, comment="股东类型")
    
    # 股东排名
    holder_rank: Mapped[Optional[Integer]] = mapped_column(Integer, nullable=True, comment="股东排名")
    
    # 是否为流通股东
    is_tradable: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, comment="是否为流通股东")
    
    __table_args__ = (
        UniqueConstraint('stock_code', 'report_period', 'holder_name', name='uk_stock_holder_code_period_name'),
        Index('idx_stock_holder_code', 'stock_code'),
        Index('idx_stock_holder_period', 'report_period'),
        Index('idx_stock_holder_type', 'holder_type'),
        Index('idx_stock_holder_rank', 'holder_rank'),
    )
    
    def __repr__(self) -> str:
        return f"<StockHolderData(stock_code={self.stock_code}, holder_name={self.holder_name}, report_period={self.report_period})>"


class StockDividendData(BaseFinancialModel):
    """
    股票分红数据模型
    存储股票的分红送股信息
    """
    __tablename__ = "stock_dividend_data"
    
    # 股票代码
    stock_code: Mapped[str] = mapped_column(String(20), nullable=False, comment="股票代码")
    
    # 分红年度
    dividend_year: Mapped[Integer] = mapped_column(Integer, nullable=False, comment="分红年度")
    
    # 公告日期
    ann_date: Mapped[Optional[date]] = mapped_column(Date, nullable=True, comment="公告日期")
    
    # 股权登记日
    record_date: Mapped[Optional[date]] = mapped_column(Date, nullable=True, comment="股权登记日")
    
    # 除权除息日
    ex_date: Mapped[Optional[date]] = mapped_column(Date, nullable=True, comment="除权除息日")
    
    # 派息日
    pay_date: Mapped[Optional[date]] = mapped_column(Date, nullable=True, comment="派息日")
    
    # 每股派息（元）
    cash_div: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 4), nullable=True, comment="每股派息(元)")
    
    # 每股送股（股）
    stock_div: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 4), nullable=True, comment="每股送股(股)")
    
    # 每股转增（股）
    stock_transfer: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 4), nullable=True, comment="每股转增(股)")
    
    # 分红方案
    div_plan: Mapped[Optional[str]] = mapped_column(String(500), nullable=True, comment="分红方案")
    
    # 实施状态：预案, 实施, 取消
    impl_status: Mapped[Optional[str]] = mapped_column(String(20), nullable=True, comment="实施状态")
    
    __table_args__ = (
        UniqueConstraint('stock_code', 'dividend_year', 'ann_date', name='uk_stock_dividend_code_year_ann'),
        Index('idx_stock_dividend_code', 'stock_code'),
        Index('idx_stock_dividend_year', 'dividend_year'),
        Index('idx_stock_dividend_ex_date', 'ex_date'),
        Index('idx_stock_dividend_status', 'impl_status'),
    )
    
    def __repr__(self) -> str:
        return f"<StockDividendData(stock_code={self.stock_code}, dividend_year={self.dividend_year}, cash_div={self.cash_div})>"


class StockMarginData(BaseFinancialModel, PartitionMixin):
    """
    股票融资融券数据模型
    存储股票的融资融券交易数据
    """
    __tablename__ = "stock_margin_data"
    
    # 股票代码
    stock_code: Mapped[str] = mapped_column(String(20), nullable=False, comment="股票代码")
    
    # 交易日期
    trade_date: Mapped[date] = mapped_column(Date, nullable=False, comment="交易日期")
    
    # 融资余额（万元）
    rzye: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="融资余额(万元)")
    
    # 融资买入额（万元）
    rzmre: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="融资买入额(万元)")
    
    # 融资偿还额（万元）
    rzche: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="融资偿还额(万元)")
    
    # 融券余量（万股）
    rqyl: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="融券余量(万股)")
    
    # 融券卖出量（万股）
    rqmcl: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="融券卖出量(万股)")
    
    # 融券偿还量（万股）
    rqchl: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="融券偿还量(万股)")
    
    # 融券余额（万元）
    rqye: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="融券余额(万元)")
    
    # 融资融券余额（万元）
    rzrqye: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="融资融券余额(万元)")
    
    __table_args__ = (
        UniqueConstraint('stock_code', 'trade_date', name='uk_stock_margin_code_date'),
        Index('idx_stock_margin_code', 'stock_code'),
        Index('idx_stock_margin_date', 'trade_date'),
        Index('idx_stock_margin_code_date', 'stock_code', 'trade_date'),
        # 分区索引
        Index('idx_stock_margin_partition_date', 'partition_date'),
    )
    
    def __repr__(self) -> str:
        return f"<StockMarginData(stock_code={self.stock_code}, trade_date={self.trade_date}, rzrqye={self.rzrqye})>"


# 导出所有股票相关模型
__all__ = [
    'StockBasicInfo',
    'StockDailyData', 
    'StockMinuteData',
    'StockTickData',
    'StockFinancialData',
    'StockHolderData',
    'StockDividendData',
    'StockMarginData',
]